{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数值替换"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 一对一替换"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A1</td>\n",
       "      <td>张通</td>\n",
       "      <td>101.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>2018-08-08 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>2018-08-09 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>2018-08-10 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>2018-08-11 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104.0</td>\n",
       "      <td>260.0</td>\n",
       "      <td>2018-08-12 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>A6</td>\n",
       "      <td>王丹</td>\n",
       "      <td>105.0</td>\n",
       "      <td>280.0</td>\n",
       "      <td>2018-08-12 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码     年龄                 成交时间\n",
       "0   A1   张通  101.0   31.0  2018-08-08 00:00:00\n",
       "1    0    0    0.0    0.0                    0\n",
       "2   A2   李谷  102.0   45.0  2018-08-09 00:00:00\n",
       "3   A3   孙凤  103.0   23.0  2018-08-10 00:00:00\n",
       "4   A4   赵恒  104.0   33.0  2018-08-11 00:00:00\n",
       "5   A5   赵恒  104.0  260.0  2018-08-12 00:00:00\n",
       "6   A6   王丹  105.0  280.0  2018-08-12 00:00:00"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.read_excel(r\"..\\Data\\Chapter07.xlsx\",sheet_name =0)\n",
    "#对某一列进行数值替换\n",
    "df[\"年龄\"].replace(240,33,inplace = True)\n",
    "df\n",
    "#对全表中的缺失值进行替换\n",
    "df.replace(np.NaN,0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 多对一替换"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A1</td>\n",
       "      <td>张通</td>\n",
       "      <td>101.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>2018-08-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>2018-08-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>2018-08-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>2018-08-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>2018-08-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>A6</td>\n",
       "      <td>王丹</td>\n",
       "      <td>105.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>2018-08-12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码    年龄       成交时间\n",
       "0   A1   张通  101.0  31.0 2018-08-08\n",
       "1  NaN  NaN    NaN   NaN        NaT\n",
       "2   A2   李谷  102.0  45.0 2018-08-09\n",
       "3   A3   孙凤  103.0  23.0 2018-08-10\n",
       "4   A4   赵恒  104.0  35.0 2018-08-11\n",
       "5   A5   赵恒  104.0  35.0 2018-08-12\n",
       "6   A6   王丹  105.0  35.0 2018-08-12"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.read_excel(r\"..\\Data\\Chapter07.xlsx\",sheet_name =0)\n",
    "#多对一用replace([A,B],C)方法，表示将A、B替换成C\n",
    "df.replace([240,260,280],35)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 多对多替换"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A1</td>\n",
       "      <td>张通</td>\n",
       "      <td>101.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>2018-08-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>2018-08-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>2018-08-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>2018-08-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>2018-08-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>A6</td>\n",
       "      <td>王丹</td>\n",
       "      <td>105.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>2018-08-12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码    年龄       成交时间\n",
       "0   A1   张通  101.0  31.0 2018-08-08\n",
       "1  NaN  NaN    NaN   NaN        NaT\n",
       "2   A2   李谷  102.0  45.0 2018-08-09\n",
       "3   A3   孙凤  103.0  23.0 2018-08-10\n",
       "4   A4   赵恒  104.0  32.0 2018-08-11\n",
       "5   A5   赵恒  104.0  33.0 2018-08-12\n",
       "6   A6   王丹  105.0  34.0 2018-08-12"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.read_excel(r\"..\\Data\\Chapter07.xlsx\",sheet_name =0)\n",
    "df\n",
    "#多对多的替换借助replace()方法，用字典的形式表示,replace({\"A\":\"a\",\"B\":\"b\"})表示用a替换A，用b替换B.\n",
    "df.replace({240:32,260:33,280:34})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数值排序"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 按照一定数值进行排序"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A5</td>\n",
       "      <td>王娜</td>\n",
       "      <td>105</td>\n",
       "      <td>21</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "      <td>2018-08-09</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>240</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A1</td>\n",
       "      <td>张通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码   年龄       成交时间  销售ID\n",
       "4   A5   王娜    105   21 2018-08-11     3\n",
       "1   A2   李谷    102   45 2018-08-09     2\n",
       "3   A4   赵恒    104  240 2018-08-11     2\n",
       "0   A1   张通    101   31 2018-08-08     1\n",
       "2   A3   孙凤    103   23 2018-08-10     1"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.read_excel(r\"..\\Data\\Chapter07.xlsx\",sheet_name =1)\n",
    "#df.sort_values(by=[\"col\"],ascending = False) 默认是升序\n",
    "#按照销售ID进行升序排序\n",
    "df.sort_values(by=[\"销售ID\"])\n",
    "#按照销售ID进行降序排序\n",
    "df.sort_values(by=[\"销售ID\"],ascending= False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 按照有缺失值的列进行排序"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A1</td>\n",
       "      <td>张通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "      <td>2018-08-09</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>36</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A5</td>\n",
       "      <td>王娜</td>\n",
       "      <td>105</td>\n",
       "      <td>21</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码  年龄       成交时间  销售ID\n",
       "2   A3   孙凤    103  23 2018-08-10   NaN\n",
       "0   A1   张通    101  31 2018-08-08   1.0\n",
       "1   A2   李谷    102  45 2018-08-09   2.0\n",
       "3   A4   赵恒    104  36 2018-08-11   2.0\n",
       "4   A5   王娜    105  21 2018-08-11   3.0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_excel(r\"..\\Data\\Chapter07.xlsx\",sheet_name =2)\n",
    "df\n",
    "#默认空值是排在最后面\n",
    "df.sort_values( by = [\"销售ID\"])\n",
    "#通过设置na_position参数将缺失的值显示在前面，默认参数值是last\n",
    "df.sort_values(by = [\"销售ID\"],na_position = \"first\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 按照多列数字进行排序"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A1</td>\n",
       "      <td>张通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>36</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "      <td>2018-08-09</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A5</td>\n",
       "      <td>王娜</td>\n",
       "      <td>105</td>\n",
       "      <td>21</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码  年龄       成交时间  销售ID\n",
       "2   A3   孙凤    103  23 2018-08-10     1\n",
       "0   A1   张通    101  31 2018-08-08     1\n",
       "3   A4   赵恒    104  36 2018-08-11     2\n",
       "1   A2   李谷    102  45 2018-08-09     2\n",
       "4   A5   王娜    105  21 2018-08-11     3"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_excel(r\"..\\Data\\Chapter07.xlsx\",sheet_name =3)\n",
    "df\n",
    "#将需要排序的by里面，然后在设置升降序\n",
    "df.sort_values(by=[\"销售ID\",\"成交时间\"],ascending = [True,False])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数值排名\n",
    "- 使用rank()方法，有两个参数，一个是ascending用来指明排列默认升序，另外一个是method，指明列有重复值是的处理情况  \n",
    "\n",
    "method | 说明\n",
    "---|---\n",
    "average|与Excel中的RANK.AVG函数功能一样 \n",
    "first|按值在所有待排列数据中出现的先后顺序排名\n",
    "min|与Excle中的RANK.EQ函数的功能一样\n",
    "max|与min相反，取重复值对应的最大排名\n",
    "\n",
    "- Excel中RANK、RANK.AVG、RANK.EQ区别\n",
    "- Rank是Excel早起版本就有的函数，而RANK.EQ是Excel2010才开始出现的，同时增加了RANK.AVG函数\n",
    "\n",
    "函数名|说明\n",
    "-|-\n",
    " RANK|根据数值获取排名，默认降序\n",
    " RANK.AVG|根据排名相同值的排名求得平均值的排名，默认降序\n",
    " RANK.EQ|与RANK的方法一致\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>订单数</th>\n",
       "      <th>RANK函数</th>\n",
       "      <th>RANK.AVG函数</th>\n",
       "      <th>RANK.EQ函数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>赵恒</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>叶枫</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>张通</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>李谷</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>5.5</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>孙凤</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>5.5</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>王娜</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>5.5</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>李斯</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>5.5</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   姓名  订单数  RANK函数  RANK.AVG函数  RANK.EQ函数\n",
       "0  赵恒   11       1         1.0          1\n",
       "1  叶枫    9       2         2.0          2\n",
       "2  张通    4       3         3.0          3\n",
       "3  李谷    1       4         5.5          4\n",
       "4  孙凤    1       4         5.5          4\n",
       "5  王娜    1       4         5.5          4\n",
       "6  李斯    1       4         5.5          4"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Excel中RANK、RANK.AVG、RANK.EQ的使用\n",
    "import pandas as pd\n",
    "df = pd.read_excel(r\"..\\Data\\Chapter07.xlsx\",sheet_name =4)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1.5\n",
       "1    3.5\n",
       "2    1.5\n",
       "3    3.5\n",
       "4    5.0\n",
       "Name: 销售ID, dtype: float64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_excel(r\"..\\Data\\Chapter07.xlsx\",sheet_name =1)\n",
    "df[\"销售ID\"]\n",
    "#method取average时与Excel中的RANK.AVG函数一样\n",
    "df[\"销售ID\"].rank(method =\"average\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1.0\n",
       "1    3.0\n",
       "2    2.0\n",
       "3    4.0\n",
       "4    5.0\n",
       "Name: 销售ID, dtype: float64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#method取first时，取数值第一次出现的排名\n",
    "df[\"销售ID\"].rank(method =\"first\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1.0\n",
       "1    3.0\n",
       "2    1.0\n",
       "3    3.0\n",
       "4    5.0\n",
       "Name: 销售ID, dtype: float64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#method取min时与Excel中的RANK.EQ函数一样\n",
    "df[\"销售ID\"].rank(method =\"min\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    2.0\n",
       "1    4.0\n",
       "2    2.0\n",
       "3    4.0\n",
       "4    5.0\n",
       "Name: 销售ID, dtype: float64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#与min相反\n",
    "df[\"销售ID\"].rank(method =\"max\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数值删除"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 删除列\n",
    "用drop()方法，在括号中设置需要删除的位置，设置参数axis = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A1</td>\n",
       "      <td>张通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A5</td>\n",
       "      <td>王娜</td>\n",
       "      <td>105</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码  年龄\n",
       "0   A1   张通    101  31\n",
       "1   A2   李谷    102  45\n",
       "2   A3   孙凤    103  23\n",
       "3   A4   赵恒    104  36\n",
       "4   A5   王娜    105  21"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#直接传入列名\n",
    "import pandas as pd\n",
    "df = pd.read_excel(r\"..\\Data\\Chapter07.xlsx\",sheet_name =1)\n",
    "df\n",
    "#axis为1时表示列，0时表示行\n",
    "df.drop([\"销售ID\",\"成交时间\"],axis =1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A1</td>\n",
       "      <td>张通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A5</td>\n",
       "      <td>王娜</td>\n",
       "      <td>105</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码  年龄\n",
       "0   A1   张通    101  31\n",
       "1   A2   李谷    102  45\n",
       "2   A3   孙凤    103  23\n",
       "3   A4   赵恒    104  36\n",
       "4   A5   王娜    105  21"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#传入列的位置\n",
    "df.drop(df.columns[[4,5]],axis =1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A1</td>\n",
       "      <td>张通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A5</td>\n",
       "      <td>王娜</td>\n",
       "      <td>105</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码  年龄\n",
       "0   A1   张通    101  31\n",
       "1   A2   李谷    102  45\n",
       "2   A3   孙凤    103  23\n",
       "3   A4   赵恒    104  36\n",
       "4   A5   王娜    105  21"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#传入列表\n",
    "df.drop(columns = [\"销售ID\",\"成交时间\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 删除行\n",
    "用drop方法，设置参数axis=0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2c</th>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3d</th>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>36</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4e</th>\n",
       "      <td>A5</td>\n",
       "      <td>王娜</td>\n",
       "      <td>105</td>\n",
       "      <td>21</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   订单编号 客户姓名  唯一识别码  年龄       成交时间  销售ID\n",
       "2c   A3   孙凤    103  23 2018-08-10     1\n",
       "3d   A4   赵恒    104  36 2018-08-11     2\n",
       "4e   A5   王娜    105  21 2018-08-11     3"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#添加行索引\n",
    "df.index = [\"0a\",\"1b\",\"2c\",\"3d\",\"4e\"]\n",
    "#传入列名称\n",
    "df.drop([\"0a\",\"1b\"],axis = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2c</th>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3d</th>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>36</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4e</th>\n",
       "      <td>A5</td>\n",
       "      <td>王娜</td>\n",
       "      <td>105</td>\n",
       "      <td>21</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   订单编号 客户姓名  唯一识别码  年龄       成交时间  销售ID\n",
       "2c   A3   孙凤    103  23 2018-08-10     1\n",
       "3d   A4   赵恒    104  36 2018-08-11     2\n",
       "4e   A5   王娜    105  21 2018-08-11     3"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#传入待删除的行号\n",
    "df.drop(df.index[[0,1]],axis = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2c</th>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3d</th>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>36</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4e</th>\n",
       "      <td>A5</td>\n",
       "      <td>王娜</td>\n",
       "      <td>105</td>\n",
       "      <td>21</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   订单编号 客户姓名  唯一识别码  年龄       成交时间  销售ID\n",
       "2c   A3   孙凤    103  23 2018-08-10     1\n",
       "3d   A4   赵恒    104  36 2018-08-11     2\n",
       "4e   A5   王娜    105  21 2018-08-11     3"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#行名直接传给index参数\n",
    "df.drop(index = [\"0a\",\"1b\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 删除特定的行\n",
    "python中不能直接删除满足条件的值，而是把不满足条件的值筛选出来作为新数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0a</th>\n",
       "      <td>A1</td>\n",
       "      <td>张通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2c</th>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3d</th>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>36</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4e</th>\n",
       "      <td>A5</td>\n",
       "      <td>王娜</td>\n",
       "      <td>105</td>\n",
       "      <td>21</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   订单编号 客户姓名  唯一识别码  年龄       成交时间  销售ID\n",
       "0a   A1   张通    101  31 2018-08-08     1\n",
       "2c   A3   孙凤    103  23 2018-08-10     1\n",
       "3d   A4   赵恒    104  36 2018-08-11     2\n",
       "4e   A5   王娜    105  21 2018-08-11     3"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#删除年龄大于40对应的行\n",
    "df[df[\"年龄\"]<40]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数值计算\n",
    "- 数值计算就是计算某个值在一个系列中数值出现的次数\n",
    "- 使用value_counts()方法，如需出现占比可以传入参数normalize = True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2    2\n",
       "1    2\n",
       "3    1\n",
       "Name: 销售ID, dtype: int64"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#计算销售ID的值出现的次数\n",
    "df[\"销售ID\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2    0.4\n",
       "1    0.4\n",
       "3    0.2\n",
       "Name: 销售ID, dtype: float64"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#计算销售ID的值占比\n",
    "df[\"销售ID\"].value_counts(normalize = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 唯一值获取\n",
    "- 唯一值获取就是把一系列值删除重复项一以后的结果\n",
    "- 使用unique()方法，返回数组"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1, 2, 3], dtype=int64)"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"销售ID\"].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数值查找\n",
    "- 就是看数据表中是否包含某个值\n",
    "- 用isin() 方法，将需要查找的值作为参数,返回布尔值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0a     True\n",
       "1b    False\n",
       "2c    False\n",
       "3d    False\n",
       "4e     True\n",
       "Name: 年龄, dtype: bool"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#单列查找\n",
    "df['年龄'].isin([31,21])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0a</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1b</th>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2c</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3d</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4e</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     订单编号   客户姓名  唯一识别码     年龄   成交时间   销售ID\n",
       "0a  False  False  False   True  False  False\n",
       "1b   True  False  False  False  False  False\n",
       "2c  False  False  False  False  False  False\n",
       "3d  False  False  False  False  False  False\n",
       "4e  False  False  False  False  False  False"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#全表查找\n",
    "df.isin([\"A2\",31])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 区间切分\n",
    "- cut()方法对区间检查切分，用参数bins来指明切分区间\n",
    "- qcut()方法也可以，但是不要事先指明切分区间，只需指明切分个数\n",
    "- 当数据分布比较均匀时，两个方法得到的区间基本一致，如果分布不均时则偏差比较大"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     (0, 3]\n",
       "1     (0, 3]\n",
       "2     (0, 3]\n",
       "3     (3, 6]\n",
       "4     (3, 6]\n",
       "5     (3, 6]\n",
       "6     (6, 9]\n",
       "7     (6, 9]\n",
       "8     (6, 9]\n",
       "9    (9, 10]\n",
       "Name: 年龄, dtype: category\n",
       "Categories (4, interval[int64]): [(0, 3] < (3, 6] < (6, 9] < (9, 10]]"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#cut切分结果是几个左开又闭的区间\n",
    "import pandas as pd\n",
    "df = pd.read_excel(r\"..\\Data\\Chapter07.xlsx\",sheet_name =5)\n",
    "df\n",
    "pd.cut(df[\"年龄\"],bins = [0,3,6,9,10])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    (0.999, 4.0]\n",
       "1    (0.999, 4.0]\n",
       "2    (0.999, 4.0]\n",
       "3    (0.999, 4.0]\n",
       "4      (4.0, 7.0]\n",
       "5      (4.0, 7.0]\n",
       "6      (4.0, 7.0]\n",
       "7     (7.0, 10.0]\n",
       "8     (7.0, 10.0]\n",
       "9     (7.0, 10.0]\n",
       "Name: 年龄, dtype: category\n",
       "Categories (3, interval[float64]): [(0.999, 4.0] < (4.0, 7.0] < (7.0, 10.0]]"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#qcut只需要指明切分个数\n",
    "pd.qcut(df[\"年龄\"],3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 插入新的行或列  \n",
    "- 在python中没有专门用来插入行的方法，可以待插入的行当做一个新的列表，然后将两个表在纵轴方向上进行拼接\n",
    "- 在python中插入一列用insert()方法实现，在扩后中指明要插入的位置、插入后新列的列名，以及要插入的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>商品类别</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A1</td>\n",
       "      <td>张通</td>\n",
       "      <td>cat01</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>cat02</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "      <td>2018-08-09</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>cat03</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>cat04</td>\n",
       "      <td>104</td>\n",
       "      <td>36</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A5</td>\n",
       "      <td>王娜</td>\n",
       "      <td>cat05</td>\n",
       "      <td>105</td>\n",
       "      <td>21</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名   商品类别  唯一识别码  年龄       成交时间  销售ID\n",
       "0   A1   张通  cat01    101  31 2018-08-08     1\n",
       "1   A2   李谷  cat02    102  45 2018-08-09     2\n",
       "2   A3   孙凤  cat03    103  23 2018-08-10     1\n",
       "3   A4   赵恒  cat04    104  36 2018-08-11     2\n",
       "4   A5   王娜  cat05    105  21 2018-08-11     3"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#用insert()方法插入列\n",
    "import pandas as pd\n",
    "df = pd.read_excel(r\"..\\Data\\Chapter07.xlsx\",sheet_name =3)\n",
    "df\n",
    "df.insert(2,\"商品类别\",[\"cat01\",\"cat02\",\"cat03\",\"cat04\",\"cat05\"])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "      <th>商品类别</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A1</td>\n",
       "      <td>张通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "      <td>1</td>\n",
       "      <td>cat01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "      <td>2018-08-09</td>\n",
       "      <td>2</td>\n",
       "      <td>cat02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>1</td>\n",
       "      <td>cat03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>36</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>2</td>\n",
       "      <td>cat04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A5</td>\n",
       "      <td>王娜</td>\n",
       "      <td>105</td>\n",
       "      <td>21</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>3</td>\n",
       "      <td>cat05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码  年龄       成交时间  销售ID   商品类别\n",
       "0   A1   张通    101  31 2018-08-08     1  cat01\n",
       "1   A2   李谷    102  45 2018-08-09     2  cat02\n",
       "2   A3   孙凤    103  23 2018-08-10     1  cat03\n",
       "3   A4   赵恒    104  36 2018-08-11     2  cat04\n",
       "4   A5   王娜    105  21 2018-08-11     3  cat05"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#通过索引的方式插入列\n",
    "import pandas as pd\n",
    "df = pd.read_excel(r\"..\\Data\\Chapter07.xlsx\",sheet_name =3)\n",
    "df[\"商品类别\"]= [\"cat01\",\"cat02\",\"cat03\",\"cat04\",\"cat05\"]\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 行列互换\n",
    "- 所谓的行列互换(又称转置)就是行将数据的转换到列方向上，将列数据转换到行方向上。  \n",
    "- 用.T方法实现"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0   2018-08-08\n",
       "1   2018-08-09\n",
       "2   2018-08-10\n",
       "3   2018-08-11\n",
       "4   2018-08-11\n",
       "Name: 成交时间, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_excel(r\"..\\Data\\Chapter07.xlsx\",sheet_name =3)\n",
    "#行列转置\n",
    "df.T\n",
    "#再转置则回到原来的结果\n",
    "df.T.T\n",
    "m=df[\"成交时间\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 索引重塑\n",
    "- 所谓的索引重塑就是将原来的索引进行重构。\n",
    "- 把数据从表格型数据转换到树形结果的过程叫重塑，用strack()方法实现\n",
    "- 用unstack()将树形数据转为表格型数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "S1  C1    1\n",
       "    C2    2\n",
       "    C3    3\n",
       "S2  C1    4\n",
       "    C2    5\n",
       "    C3    6\n",
       "dtype: int64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_excel(r\"..\\Data\\Chapter07.xlsx\",sheet_name =6)\n",
    "df\n",
    "df.stack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>C1</th>\n",
       "      <th>C2</th>\n",
       "      <th>C3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>S1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S2</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    C1  C2  C3\n",
       "S1   1   2   3\n",
       "S2   4   5   6"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#将树形转为表格型\n",
    "df.stack().unstack()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 长宽表转换\n",
    "长宽表转换就是将比较长(很多行)的表转为比较宽的表(很多列)的表，或者将比较宽的表转为比较长的表"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 宽表转为长表  \n",
    "Python中用stack()或melt()方法实现"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Company</th>\n",
       "      <th>Name</th>\n",
       "      <th>Year</th>\n",
       "      <th>sale</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Apple</td>\n",
       "      <td>苹果</td>\n",
       "      <td>Sale2013</td>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Apple</td>\n",
       "      <td>苹果</td>\n",
       "      <td>Sale2014</td>\n",
       "      <td>5050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Apple</td>\n",
       "      <td>苹果</td>\n",
       "      <td>Sale2015</td>\n",
       "      <td>5050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Apple</td>\n",
       "      <td>苹果</td>\n",
       "      <td>Sale2016</td>\n",
       "      <td>5050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Google</td>\n",
       "      <td>谷歌</td>\n",
       "      <td>Sale2013</td>\n",
       "      <td>3500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Google</td>\n",
       "      <td>谷歌</td>\n",
       "      <td>Sale2014</td>\n",
       "      <td>3800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Google</td>\n",
       "      <td>谷歌</td>\n",
       "      <td>Sale2015</td>\n",
       "      <td>3800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Google</td>\n",
       "      <td>谷歌</td>\n",
       "      <td>Sale2016</td>\n",
       "      <td>3800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Facebook</td>\n",
       "      <td>脸书</td>\n",
       "      <td>Sale2013</td>\n",
       "      <td>2300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Facebook</td>\n",
       "      <td>脸书</td>\n",
       "      <td>Sale2014</td>\n",
       "      <td>2900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Facebook</td>\n",
       "      <td>脸书</td>\n",
       "      <td>Sale2015</td>\n",
       "      <td>2900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Facebook</td>\n",
       "      <td>脸书</td>\n",
       "      <td>Sale2016</td>\n",
       "      <td>2900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Company</td>\n",
       "      <td>Name</td>\n",
       "      <td>Sale2013</td>\n",
       "      <td>Sale2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Company</td>\n",
       "      <td>Name</td>\n",
       "      <td>Sale2014</td>\n",
       "      <td>Sale2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Company</td>\n",
       "      <td>Name</td>\n",
       "      <td>Sale2015</td>\n",
       "      <td>Sale2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Company</td>\n",
       "      <td>Name</td>\n",
       "      <td>Sale2016</td>\n",
       "      <td>Sale2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Apple</td>\n",
       "      <td>苹果</td>\n",
       "      <td>Sale2013</td>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Apple</td>\n",
       "      <td>苹果</td>\n",
       "      <td>Sale2014</td>\n",
       "      <td>5050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Apple</td>\n",
       "      <td>苹果</td>\n",
       "      <td>Sale2015</td>\n",
       "      <td>5050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Apple</td>\n",
       "      <td>苹果</td>\n",
       "      <td>Sale2016</td>\n",
       "      <td>5050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Google</td>\n",
       "      <td>谷歌</td>\n",
       "      <td>Sale2013</td>\n",
       "      <td>3500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Google</td>\n",
       "      <td>谷歌</td>\n",
       "      <td>Sale2014</td>\n",
       "      <td>3800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Google</td>\n",
       "      <td>谷歌</td>\n",
       "      <td>Sale2015</td>\n",
       "      <td>3800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Google</td>\n",
       "      <td>谷歌</td>\n",
       "      <td>Sale2016</td>\n",
       "      <td>3800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Facebook</td>\n",
       "      <td>脸书</td>\n",
       "      <td>Sale2013</td>\n",
       "      <td>2300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Facebook</td>\n",
       "      <td>脸书</td>\n",
       "      <td>Sale2014</td>\n",
       "      <td>2900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Facebook</td>\n",
       "      <td>脸书</td>\n",
       "      <td>Sale2015</td>\n",
       "      <td>2900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Facebook</td>\n",
       "      <td>脸书</td>\n",
       "      <td>Sale2016</td>\n",
       "      <td>2900</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Company  Name      Year      sale\n",
       "0      Apple    苹果  Sale2013      5000\n",
       "1      Apple    苹果  Sale2014      5050\n",
       "2      Apple    苹果  Sale2015      5050\n",
       "3      Apple    苹果  Sale2016      5050\n",
       "4     Google    谷歌  Sale2013      3500\n",
       "5     Google    谷歌  Sale2014      3800\n",
       "6     Google    谷歌  Sale2015      3800\n",
       "7     Google    谷歌  Sale2016      3800\n",
       "8   Facebook    脸书  Sale2013      2300\n",
       "9   Facebook    脸书  Sale2014      2900\n",
       "10  Facebook    脸书  Sale2015      2900\n",
       "11  Facebook    脸书  Sale2016      2900\n",
       "12   Company  Name  Sale2013  Sale2013\n",
       "13   Company  Name  Sale2014  Sale2014\n",
       "14   Company  Name  Sale2015  Sale2015\n",
       "15   Company  Name  Sale2016  Sale2016\n",
       "16     Apple    苹果  Sale2013      5000\n",
       "17     Apple    苹果  Sale2014      5050\n",
       "18     Apple    苹果  Sale2015      5050\n",
       "19     Apple    苹果  Sale2016      5050\n",
       "20    Google    谷歌  Sale2013      3500\n",
       "21    Google    谷歌  Sale2014      3800\n",
       "22    Google    谷歌  Sale2015      3800\n",
       "23    Google    谷歌  Sale2016      3800\n",
       "24  Facebook    脸书  Sale2013      2300\n",
       "25  Facebook    脸书  Sale2014      2900\n",
       "26  Facebook    脸书  Sale2015      2900\n",
       "27  Facebook    脸书  Sale2016      2900"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_excel(r\"..\\Data\\Chapter07.xlsx\",sheet_name =7)\n",
    "df\n",
    "#设置索引\n",
    "df.set_index([\"Company\",\"Name\"])\n",
    "#将列索引转为行索引\n",
    "df.set_index([\"Company\",\"Name\"]).stack()\n",
    "#重置索引\n",
    "df.set_index([\"Company\",\"Name\"]).stack().reset_index()\n",
    "#重命名索引\n",
    "df.set_index([\"Company\",\"Name\"]).stack().reset_index().rename(columns={\"level_2\":\"Year\",0:\"sale\"})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "melt中的id_vars参数用于指明宽表转黄到成都表时保持不变的列，var_name参数表示原来的列索引转化为“行索引”以后的列名，value_name表示新索引对于的值的列名  \n",
    "**注意：**这里的“行索引”是有双引号的，并非实际行索引，只是类似的实际行索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Company</th>\n",
       "      <th>Name</th>\n",
       "      <th>Year</th>\n",
       "      <th>Sale</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Apple</td>\n",
       "      <td>苹果</td>\n",
       "      <td>Sale2013</td>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Google</td>\n",
       "      <td>谷歌</td>\n",
       "      <td>Sale2013</td>\n",
       "      <td>3500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Facebook</td>\n",
       "      <td>脸书</td>\n",
       "      <td>Sale2013</td>\n",
       "      <td>2300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Apple</td>\n",
       "      <td>苹果</td>\n",
       "      <td>Sale2014</td>\n",
       "      <td>5050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Google</td>\n",
       "      <td>谷歌</td>\n",
       "      <td>Sale2014</td>\n",
       "      <td>3800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Facebook</td>\n",
       "      <td>脸书</td>\n",
       "      <td>Sale2014</td>\n",
       "      <td>2900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Apple</td>\n",
       "      <td>苹果</td>\n",
       "      <td>Sale2015</td>\n",
       "      <td>5050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Google</td>\n",
       "      <td>谷歌</td>\n",
       "      <td>Sale2015</td>\n",
       "      <td>3800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Facebook</td>\n",
       "      <td>脸书</td>\n",
       "      <td>Sale2015</td>\n",
       "      <td>2900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Apple</td>\n",
       "      <td>苹果</td>\n",
       "      <td>Sale2016</td>\n",
       "      <td>5050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Google</td>\n",
       "      <td>谷歌</td>\n",
       "      <td>Sale2016</td>\n",
       "      <td>3800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Facebook</td>\n",
       "      <td>脸书</td>\n",
       "      <td>Sale2016</td>\n",
       "      <td>2900</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Company Name      Year  Sale\n",
       "0      Apple   苹果  Sale2013  5000\n",
       "1     Google   谷歌  Sale2013  3500\n",
       "2   Facebook   脸书  Sale2013  2300\n",
       "3      Apple   苹果  Sale2014  5050\n",
       "4     Google   谷歌  Sale2014  3800\n",
       "5   Facebook   脸书  Sale2014  2900\n",
       "6      Apple   苹果  Sale2015  5050\n",
       "7     Google   谷歌  Sale2015  3800\n",
       "8   Facebook   脸书  Sale2015  2900\n",
       "9      Apple   苹果  Sale2016  5050\n",
       "10    Google   谷歌  Sale2016  3800\n",
       "11  Facebook   脸书  Sale2016  2900"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#用melt()方法实现\n",
    "import pandas as pd\n",
    "df = pd.read_excel(r\"..\\Data\\Chapter07.xlsx\",sheet_name =7)\n",
    "df\n",
    "df.melt(id_vars=[\"Company\",\"Name\"],var_name=\"Year\",value_name = \"Sale\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 长表转为宽表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>Sale2013</th>\n",
       "      <th>Sale2014</th>\n",
       "      <th>Sale2015</th>\n",
       "      <th>Sale2016</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Company</th>\n",
       "      <th>Name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Apple</th>\n",
       "      <th>苹果</th>\n",
       "      <td>5000</td>\n",
       "      <td>5050</td>\n",
       "      <td>5050</td>\n",
       "      <td>5050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Facebook</th>\n",
       "      <th>脸书</th>\n",
       "      <td>2300</td>\n",
       "      <td>2900</td>\n",
       "      <td>2900</td>\n",
       "      <td>2900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Google</th>\n",
       "      <th>谷歌</th>\n",
       "      <td>3500</td>\n",
       "      <td>3800</td>\n",
       "      <td>3800</td>\n",
       "      <td>3800</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Year           Sale2013  Sale2014  Sale2015  Sale2016\n",
       "Company  Name                                        \n",
       "Apple    苹果        5000      5050      5050      5050\n",
       "Facebook 脸书        2300      2900      2900      2900\n",
       "Google   谷歌        3500      3800      3800      3800"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_excel(r\"..\\Data\\Chapter07Chapter07.xlsx\",sheet_name =8)\n",
    "df\n",
    "df.pivot_table(index=[\"Company\",\"Name\"],columns=\"Year\",values=\"Sale\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## apply()与applymap()函数  \n",
    "- apply()函数主要用于对DataFrame中的某一column或row中的元素执行相同的函数操作\n",
    "- applymap()函数主要用于对DataFrame中每一个元素执行系统的操作\n",
    "- apply()和applymap()都要与lambda结合使用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>C1</th>\n",
       "      <th>C2</th>\n",
       "      <th>C3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   C1  C2  C3\n",
       "0   1   2   3\n",
       "1   4   5   6\n",
       "2   7   8   9"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#对C1列的远隔元素加1\n",
    "import pandas as pd\n",
    "df = pd.read_excel(r\"..\\Data\\Chapter07.xlsx\",sheet_name =9)\n",
    "df\n",
    "df[\"C1\"].apply(lambda x:x+1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>C1</th>\n",
       "      <th>C2</th>\n",
       "      <th>C3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   C1  C2  C3\n",
       "0   2   3   4\n",
       "1   5   6   7\n",
       "2   8   9  10"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#对表内的每个元素加1\n",
    "df.applymap(lambda x:x+1)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "第7章 数值操作",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
